package org.jeecg.modules.demo.crm.mapper;

import java.math.BigDecimal;
import java.util.List;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.jeecg.modules.demo.crm.entity.CContract;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;

/**
 * @Description: 合同管理记录表
 * @Author: jeecg-boot
 * @Date:   2025-06-16
 * @Version: V1.0
 */
public interface CContractMapper extends BaseMapper<CContract> {

    @Update("update c_contract set project_id=null where project_id = #{projectId} and sub_project_id=null ")
    void unbinding(@Param("projectId") String projectId);

    @Update("update c_contract set project_id=#{projectId},sub_project_id=null where id = #{id}")
    void banding(@Param("id") String id,@Param("projectId") String projectId);

    @Select("select IFNULL(sum(tax_including_price),0)  from c_contract where project_id=#{projectId} and (sub_project_id IS  NULL or sub_project_id = '') ")
    BigDecimal contractPrice(@Param("projectId") String projectId);


    @Select("select GROUP_CONCAT(id SEPARATOR ',') AS contractFile  from c_contract where project_id=#{projectId} and (sub_project_id IS  NULL or sub_project_id = '') ")
    String contractByProjectId(@Param("projectId") String projectId);


    @Select("select IFNULL(sum(tax_including_price),0)  from c_contract where project_id=#{projectId} and sub_project_id IS NOT NULL AND sub_project_id <> '' ")
    BigDecimal contractSubProjectPrice(@Param("projectId") String projectId);

    @Select("<script> select IFNULL(sum(contract_amount),0)  from c_contract where project_id=#{projectId} <if test='subProjectIds != null and subProjectIds.size() > 0'> AND  sub_project_id IN <foreach collection='subProjectIds' item='id' open='(' separator=',' close=')'>#{id}</foreach></if> </script>")
    BigDecimal contractSubProjectPriceBySubProjectId(@Param("projectId") String projectId,@Param("subProjectIds") List<String> subProjectIds);


    @Select("select * from c_contract where project_id=#{projectId} and (sub_project_id IS  NULL or sub_project_id = '')  ")
    List<CContract> findByProjectId(@Param("projectId")String projectId);

    @Select("select IFNULL(sum(tax_including_price),0)  from c_contract where (sub_project_id IS  NULL or sub_project_id = '') and project_id=#{projectId} ")
    BigDecimal sumPrice(@Param("projectId")String projectId);

    @Select("select IFNULL(sum(tax_including_price),0)  from c_contract where (sub_project_id IS NULL OR sub_project_id = '') AND project_id=#{projectId} AND  contract_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01') AND contract_date <= DATE_FORMAT(LAST_DAY(CURDATE()), '%Y-%m-%d') ")
    BigDecimal sumMonthMPrice(@Param("projectId")String projectId);

    @Select("select IFNULL(sum(warranty_price),0)  from c_contract where project_id=#{projectId} and (sub_project_id IS  NULL or sub_project_id = '') ")
    BigDecimal guaranteeDeposit(@Param("projectId")String projectId);



    @Select("SELECT CONCAT(MIN(warranty_time_start), ' - ', MAX(warranty_time_end)) AS result FROM c_contract where project_id=#{projectId}")
    String warrantyPeriod(@Param("projectId")String projectId);


    @Select("select IFNULL(sum(tax_including_price),0)  from c_contract where project_id=#{projectId} and sub_project_id=#{subProjectId}  ")
    BigDecimal subProjectIdContract(@Param("projectId")String projectId,@Param("subProjectId") String subProjectId);

    @Select("SELECT GROUP_CONCAT(contract_file SEPARATOR ',') AS contractFile FROM c_contract WHERE project_id=#{projectId} and sub_project_id=#{subProjectId} and contract_file IS NOT NULL AND contract_file <> ''; ")
    String getContractFile(@Param("projectId")String projectId,@Param("subProjectId") String subProjectId);



    @Select(" select count(*)   from c_contract where project_id = #{projectId} and sub_project_id = #{subProjectId} ")
    Integer isContractBySubProject(@Param("projectId") String projectId,@Param("subProjectId") String subProjectId);


    @Select("select count(*)   from c_contract where project_id = #{projectId} ")
    Integer isContract(@Param("projectId") String projectId);
}
